package sg.edu.nus.iss.phoenix.schedule.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.iss.phoenix.dao.DBConnection;
import sg.edu.nus.iss.phoenix.schedule.dao.ProgramSlotDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.ProgramSlot;
import sg.edu.nus.iss.phoenix.schedule.entity.WeeklySchedule;

/**
 * @author Wang Shunde
 *
 */
public class ProgramSlotDAOImpl implements ProgramSlotDAO {
	
	private Connection connection;
	
	private static final String SQL_CREATEPROGRAMSLOT = "insert into program_slot_mst(radio_pgm_id,  producer_user_id, "
			+ "present_user_id, pgm_end_time, pgm_start_time, date_of_week, created_by, created_dt, modified_by, modified_dt, schedule_id) values (?,?,?,?,?,?,?,?,?,?,?)";

	private static final String FIND_PROGRAM_SLOTS_FROM_SCHEDULE_ID = "SELECT * FROM program_slot_mst WHERE schedule_id=?";
	private static final String REMOVE_PROGRAMSLOT = "DELETE FROM program_slot_mst where program_slot_id=?";
	private static final String UPDATE_PROGRAMSLOT = "UPDATE program_slot_mst SET radio_pgm_id=?, producer_user_id=?, present_user_id=?," 
			+ "pgm_end_time=?, pgm_start_time=?, date_of_week=?, modified_by=?, modified_dt=? where program_slot_id=?";
	
	private static final String FIND_PROGRAMSLOT_FROM_ID = "SELECT * FROM program_slot_mst WHERE program_slot_id=?";
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ProgramSlotDAO#saveProgramSlot(sg.edu.nus.iss.phoenix.schedule.entity.WeeklySchedule)
	 */
	public void saveProgramSlot(WeeklySchedule weeklySchedule) throws SQLException{
		openConnection();
		PreparedStatement pstmt = null;
		
		try{
			ArrayList <ProgramSlot> programSlotsforWeek = weeklySchedule.getProgramSlotsforWeek();
			ProgramSlot ps;
			Date now = new java.sql.Date(Utility.now.getTime());
			for(int i=0; i <programSlotsforWeek.size();i++ ){
				ps=programSlotsforWeek.get(i);
				pstmt = connection.prepareStatement(SQL_CREATEPROGRAMSLOT);
				pstmt.setLong(1, ps.getRadioProgramId());
				pstmt.setLong(2, ps.getProducerId());
				pstmt.setLong(3, ps.getPresenterId());
				pstmt.setTime(4, ps.getEndTime());
				pstmt.setTime(5, ps.getStartTime());
				pstmt.setDate(6, ps.getDateofWeek());
				pstmt.setLong(7, weeklySchedule.getUser_id());
				pstmt.setDate(8, now);
				pstmt.setLong(9, weeklySchedule.getUser_id());
				pstmt.setDate(10, now);
				pstmt.setLong(11, weeklySchedule.getId());
				pstmt.execute();
			}
		}
		finally {
			if(pstmt !=null)
				pstmt.close();
			closeConnection();
		}
	}

	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ProgramSlotDAO#searchMatching(long)
	 */
	@Override
	public List<ProgramSlot> searchMatching(long selectedWeeklSyScheduleId) throws SQLException{
		openConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<ProgramSlot> pslots = new ArrayList<ProgramSlot>();
		
		try {
			ps = connection.prepareStatement(FIND_PROGRAM_SLOTS_FROM_SCHEDULE_ID);
			ps.setLong(1, selectedWeeklSyScheduleId);
			rs = ps.executeQuery();
				
			while (rs.next()) {
				ProgramSlot pslot = new ProgramSlot();
				pslot.setId(rs.getLong("program_slot_id"));
				pslot.setDateofWeek(rs.getDate("date_of_week"));
				pslot.setStartTime(rs.getTime("pgm_start_time"));
				pslot.setEndTime(rs.getTime("pgm_end_time"));
				pslot.setPresenterId(rs.getLong("present_user_id"));
				pslot.setProducerId(rs.getLong("producer_user_id"));
				pslot.setRadioProgramId(rs.getLong("radio_pgm_id"));
				pslot.setWeeklyScheduleId(rs.getLong("schedule_id"));
				pslots.add(pslot);
			}
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
		return pslots;
	}
	public ProgramSlot searchMatchingProgramSlot(long programSlotId) throws SQLException {
		openConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ProgramSlot pslot = new ProgramSlot();	
		try {
			ps = connection.prepareStatement(FIND_PROGRAMSLOT_FROM_ID);
			ps.setLong(1, programSlotId);
			rs = ps.executeQuery();
			while (rs.next()) {
				pslot.setId(rs.getLong("program_slot_id"));
				pslot.setDateofWeek(rs.getDate("date_of_week"));
				pslot.setStartTime(rs.getTime("pgm_start_time"));
				pslot.setEndTime(rs.getTime("pgm_end_time"));
				pslot.setPresenterId(rs.getLong("present_user_id"));
				pslot.setProducerId(rs.getLong("producer_user_id"));
				pslot.setRadioProgramId(rs.getLong("radio_pgm_id"));
				pslot.setWeeklyScheduleId(rs.getLong("schedule_id"));
			}
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
		return pslot;
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ProgramSlotDAO#deleteProgramSlot(long)
	 */
	@Override
	public void deleteProgramSlot(long programSlotId) throws SQLException {
		openConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(REMOVE_PROGRAMSLOT);
			ps.setLong(1, programSlotId);
			ps.execute();
		}
		finally {
			if(ps !=null)
				ps.close();
			closeConnection();
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.ProgramSlotDAO#updateProgramSlot(sg.edu.nus.iss.phoenix.schedule.entity.ProgramSlot, long)
	 */
	@Override
	public void updateProgramSlot(ProgramSlot ps, long userId) throws SQLException {
		openConnection();
		PreparedStatement preparedstatement = null;
		try{
			preparedstatement = connection.prepareStatement(UPDATE_PROGRAMSLOT);
			Date now = new java.sql.Date(Utility.now.getTime());
			
			preparedstatement.setLong(1, ps.getRadioProgramId());
			preparedstatement.setLong(2, ps.getProducerId());
			preparedstatement.setLong(3, ps.getPresenterId());
			preparedstatement.setTime(4, ps.getEndTime());
			preparedstatement.setTime(5, ps.getStartTime());
			preparedstatement.setDate(6, ps.getDateofWeek());
			preparedstatement.setLong(7, userId);
			preparedstatement.setDate(8, now);
			preparedstatement.setLong(9, ps.getId());
			preparedstatement.execute();
		}
		finally {
			if(preparedstatement !=null)
				preparedstatement.close();
			closeConnection();
		}
	}
	
	/**
	 * Utility method to open connection to database.
	 */
	private void openConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			this.connection = dbConnection.getDBConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * Utility method to close database connection.
	 */
	private void closeConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			dbConnection.closeConnection(this.connection);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
